Postgres automatically escapes all occurrences of escape sequences if strings aren't prefixed with the E escape constant. This can lead to unexpected results:

select array_to_string(array['first line', 'second line', 'third line'], '\n');

           array_to_string
-------------------------------------
 first line\nsecond line\nthird line

Prefixing the separator string with the escape constant tells Postgres to interpret the sequence literally.

select array_to_string(array['first line', 'second line', 'third line'], E'\n');

 array_to_string
-----------------
 first line     +
 second line    +
 third line

There's no need to prefix the template string when using format, as E-strings are substituted in literally.

select format('first line%ssecond line', E'\n');

   format
-------------
 first line +
 second line

Problem

The following .sql script will error out prematurely:

create table if not exists foo (
	bar text not null,
	baz text not null
);

copy foo (bar, baz) from stdin (format csv, delimiter ';', header true);
bar;baz
Lorem;ipsum
dolor;sit
amet,;consectetur
\.

Here's the accompanying psql output:

CREATE TABLE
psql:<stdin>:11: ERROR:  missing data for column "baz"
CONTEXT:  COPY foo, line 5: "\."

Solution

Adding a newline after the \. termination sequence fixes the error.

Sometimes it's necessary to manually specify a constraint name, which should then ideally follow some sort of naming convention or pattern.

Postgres already has an implicit naming convention in place, which goes like this:

{tablename}_{columnname(s)}_{suffix}

• pkey for primary key constraints

  • Single column

    create table foo (
      bar integer primary key
    );

                                Table "public.foo"
     Column |  Type   | Collation | Nullable |             Default
    --------+---------+-----------+----------+---------------------------------
     bar    | integer |           | not null |
    Indexes:
        "foo_pkey" PRIMARY KEY, btree (bar)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      primary key (bar, bar)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           | not null |
     baz    | integer |           | not null |
    Indexes:
        "foo_pkey" PRIMARY KEY, btree (bar, baz)

• key for unique constraints

  • Single column

    create table foo (
      bar integer unique
    );

                Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
    Indexes:
      "foo_bar_key" UNIQUE CONSTRAINT, btree (bar)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      unique (bar, baz)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
     baz    | integer |           |          |
    Indexes:
        "foo_bar_baz_key" UNIQUE CONSTRAINT, btree (bar, baz)

• excl for exclusion constraints

  create table foo (
    bar text,
    baz text,
    exclude using gist (bar with =, baz with =)
  );

               Table "public.foo"
   Column | Type | Collation | Nullable | Default
  --------+------+-----------+----------+---------
   bar    | text |           |          |
   baz    | text |           |          |
  Indexes:
      "foo_bar_baz_excl" EXCLUDE USING gist (bar WITH =, baz WITH =)

• idx for indices

  Indices can not be created without manually specifying a name.

• fkey for foreign key constraints

  • Single column

    create table foo (
      bar integer primary key
    );
    create table qux (
      bar integer references foo
    );

                    Table "public.qux"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
    Foreign-key constraints:
        "qux_bar_fkey" FOREIGN KEY (bar) REFERENCES foo(bar)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      primary key(bar, baz)
    );
    create table qux (
      bar integer,
      baz integer,
      foreign key(bar, baz) references foo (bar, baz)
    );

                    Table "public.qux"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
     baz    | integer |           |          |
    Foreign-key constraints:
        "qux_bar_fkey" FOREIGN KEY (bar, baz) REFERENCES foo(bar, baz)

• check for check constraints

  • Single column

    create table foo (
      bar integer check (id > 10)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
    Check constraints:
        "foo_bar_check" CHECK (id > 10)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      check (bar = baz)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
     baz    | integer |           |          |
    Check constraints:
        "foo_check" CHECK (bar = baz)

• seq for sequences

  create table foo (
    id serial
  );

                              Table "public.foo"
  Column |  Type   | Collation | Nullable |             Default
  --------+---------+-----------+----------+---------------------------------
   id     | integer |           | not null | nextval('foo_id_seq'::regclass)